<?php
$timezone = "Asia/Manila";

if(function_exists('date_default_timezone_set')) date_default_timezone_set($timezone);

function is_date_str($date_pass){
    $count_date_error = 0;
    $date_struct = date_parse($date_pass);
    if( empty($date_struct['year']) ){
        $count_date_error++;
    }
    if( empty($date_struct['month']) ){
        $count_date_error++;
    }
    if( empty($date_struct['day']) ){
        $count_date_error++;
    }



    if( !empty($date_struct['warning_count']) ){
        if($date_struct['warning_count'] >= 1){
            $count_date_error++;
        }
    }
    if( !empty($date_struct['warnings']) ){
        if(count($date_struct['warnings']) >= 1){
            $count_date_error++;
        }
    }
    if( !empty($date_struct['error_count']) ){
        if($date_struct['error_count'] >= 1){
            $count_date_error++;
        }
    }
    if( !empty($date_struct['errors']) ){
        if(count($date_struct['errors']) >= 1){
            $count_date_error++;
        }
    }

    if($count_date_error >= 1){
        return false;
    }else{
        return true;
    }
}

function AdjustDate($type, $date, $added_value=''){
    if(!is_date_str($date)){
        $date = date('Y-m-d H:i:s', $date);
    }
    $value_add = (float)$added_value;
    switch ($type) {
        case 'day':
            $date = date("Y-m-d H:i", strtotime(date("Y-m-d H:i", strtotime($date)) . " ".$value_add." day"));
            break;
        case 'month':
            $date = date("Y-m-d H:i", strtotime(date("Y-m-d H:i", strtotime($date)) . " ".$value_add." month"));
            break;
        case 'year':
            $date = date("Y-m-d H:i", strtotime(date("Y-m-d H:i", strtotime($date)) . " ".$value_add." year"));
            break;
        case 'minute':
            $date = date("Y-m-d H:i", strtotime(date("Y-m-d H:i", strtotime($date)) . " ".$value_add." minute"));
            break;
        case 'hour':
            $date = date("Y-m-d H:i", strtotime(date("Y-m-d H:i", strtotime($date)) . " ".$value_add." day"));
            break;
        case 'week':
            $date = date("Y-m-d H:i", strtotime(date("Y-m-d H:i", strtotime($date)) . " ".$value_add." week"));
            break;
    }
    return $date;
}

function CountDayIf($start_date, $end_date){
    $args = func_get_args();
    if(!is_date_str($start_date)){
        $start_date = date('Y-m-d', $start_date);
    }
    if(!is_date_str($end_date)){
        $end_date = date('Y-m-d', $end_date);
    }
    $counted_if_days = 0;


    if( isset($args[2]) ){
        $days_difference = DiffDate('day',$end_date, $start_date);
        for($diff_ctr = 0; $diff_ctr <= $days_difference; $diff_ctr++){
            if ($diff_ctr == 0) {
                $adjusted_day = FormatDate(AdjustDate('day', $start_date, 0), 'l');
                $start_date = FormatDate(AdjustDate('day', $start_date, 0), 'Y-m-d');
            } else {
                $adjusted_day = FormatDate(AdjustDate('day', $start_date, 1), 'l');
                $start_date = FormatDate(AdjustDate('day', $start_date, 1), 'Y-m-d');
            }

            for($day_names_ctr = 0; $day_names_ctr < count($args); $day_names_ctr++){
                $param_day = $args[$day_names_ctr];
                if($adjusted_day == $param_day && $day_names_ctr >= 2){
                    $counted_if_days++;
                }
            }
        }
        return $counted_if_days;
    }else{
        return $counted_if_days;
    }
}

function FormatDate($passed_date, $format = 'Y-m-d H:i'){
    if(!is_date_str($passed_date)){
        $passed_date = date('Y-m-d H:i:s', $passed_date);
    }
	return date($format,strtotime($passed_date));
}

function DiffDate($type, $date1, $date2){
    if(!is_date_str($date1)){
        $date1 = date('Y-m-d H:i:s', $date1);
    }
    if(!is_date_str($date2)){
        $date2 = date('Y-m-d H:i:s', $date2);
    }
    switch ($type) {
        case 'day':
            $now = strtotime($date1); // or your date as well
            $your_date = strtotime($date2);
            $datediff = $now - $your_date;
            $ret_val = floor($datediff/(60*60*24));
            break;
        case 'month':
            $now = strtotime($date1); // or your date as well
            $your_date = strtotime($date2);
            $datediff = $now - $your_date;
            
            $years = floor($datediff / (365*60*60*24));
            $months = floor(($datediff - $years * 365*60*60*24) / (30*60*60*24));
            $days = floor(($datediff - $years * 365*60*60*24 - $months*30*60*60*24)/ (60*60*24));
            
            $ret_val = $months;
            break;
        case 'year':
            $now = strtotime($date1); // or your date as well
            $your_date = strtotime($date2);
            $datediff = $now - $your_date;
            
            $years = floor($datediff / (365*60*60*24));
            $months = floor(($datediff - $years * 365*60*60*24) / (30*60*60*24));
            $days = floor(($datediff - $years * 365*60*60*24 - $months*30*60*60*24)/ (60*60*24));
            
            $ret_val = $years;
            break;
        case 'minute':
            $to_time = strtotime($date1);
            $from_time = strtotime($date2);
            $ret_val = round(($to_time - $from_time) / 60,2);
            break;
        case 'hour':
            $hourdiff = round((strtotime($date1) - strtotime($date2))/3600, 1);
            $ret_val = $hourdiff;
            break;
        case 'week':
            
            break;
    }
    return $ret_val;
}
function LookupGetMax($FormName = null, $ReturnField = null, $WhereFilterName = null, $Operator = null, $WhereSearchName = null){
    $companyFilter = "";
    
    if(Auth::hasAuth('current_user')){
	$auth = Auth::getAuth('current_user');
	$companyFilter = "AND company_id = {$auth['company_id']} ";
    }else{
        //$GLOBAL['company_id']
        $companyFilter = "AND company_id = {$GLOBALS['company_id']} ";
    }
    
    $arguments = func_get_args();
    //if (empty($WhereFilterName)) {
        
    //} else {
        $db = new Database();
        $q_get_table_form_name = "SELECT `id`, `form_table_name` FROM `tb_workspace` WHERE `form_name` LIKE '" . $FormName . "' " . $companyFilter;
        // print_r($q_get_table_form_name);
        $this_get_table_form = $db->query($q_get_table_form_name, "row");
        // print_r($this_get_table_form);
    
        $q_string = "SELECT MAX(`$ReturnField`) AS MaxVal FROM `" . $this_get_table_form["form_table_name"] . "`";
        // echo $q_string;
        $Field_Name_Counter = 2;
        $Field_Operator_Counter = 3;
        $Filter_Value_Counter = 4;
        if(isset($arguments[2])){
            $q_string .= " WHERE ";
            foreach ($arguments as $key => $value) {
                // echo "BT".$key;
                if($key >= 2 and $key % $Field_Name_Counter == 0){
                    $q_string .= " `".$arguments[$key]."` ";
                    $Field_Name_Counter = $Field_Name_Counter + 3;
                }else if($key >= 3 and $key % $Field_Operator_Counter == 0){
                    $q_string .= " ".$arguments[$key]." ";
                    $Field_Operator_Counter = $Field_Operator_Counter + 3;
                }else if($key >= 4 and $key % $Filter_Value_Counter == 0){
                    $q_string .= " '".$arguments[$key]."' ";
                    $Filter_Value_Counter = $Filter_Value_Counter + 3;
                    if($key == (count($arguments)-1) ){
                        $q_string .= " ; ";
                    }else{
                        $q_string .= " AND ";

                    }
                }
            }
        }
        
        // echo $q_string;
        $this_record = $db->query($q_string, "array");
        // print_r($this_record);
        return $this_record[0]["MaxVal"];
        // return $q_string;
    //}
}
function LookupGetMin($FormName = null, $ReturnField = null, $WhereFilterName = null, $Operator = null, $WhereSearchName = null){
	$companyFilter = "";
    
    if(Auth::hasAuth('current_user')){
	$auth = Auth::getAuth('current_user');
	$companyFilter = "AND company_id = {$auth['company_id']} ";
    }else{
        //$GLOBAL['company_id']
        $companyFilter = "AND company_id = {$GLOBALS['company_id']} ";
    }
    
    $arguments = func_get_args();
    //if (empty($WhereFilterName)) {
        
    //} else {
        $db = new Database();
        $q_get_table_form_name = "SELECT `id`, `form_table_name` FROM `tb_workspace` WHERE `form_name` LIKE '" . $FormName . "' " . $companyFilter;
        // print_r($q_get_table_form_name);
        $this_get_table_form = $db->query($q_get_table_form_name, "row");
        // print_r($this_get_table_form);
    
        $q_string = "SELECT MIN(`$ReturnField`) AS MinVal FROM `" . $this_get_table_form["form_table_name"] . "`";
        // echo $q_string;
        $Field_Name_Counter = 2;
        $Field_Operator_Counter = 3;
        $Filter_Value_Counter = 4;
        if(isset($arguments[2])){
            $q_string .= " WHERE ";
            foreach ($arguments as $key => $value) {
                // echo "BT".$key;
                if($key >= 2 and $key % $Field_Name_Counter == 0){
                    $q_string .= " `".$arguments[$key]."` ";
                    $Field_Name_Counter = $Field_Name_Counter + 3;
                }else if($key >= 3 and $key % $Field_Operator_Counter == 0){
                    $q_string .= " ".$arguments[$key]." ";
                    $Field_Operator_Counter = $Field_Operator_Counter + 3;
                }else if($key >= 4 and $key % $Filter_Value_Counter == 0){
                    $q_string .= " '".$arguments[$key]."' ";
                    $Filter_Value_Counter = $Filter_Value_Counter + 3;
                    if($key == (count($arguments)-1) ){
                        $q_string .= " ; ";
                    }else{
                        $q_string .= " AND ";

                    }
                }
            }
        }
        
        // echo $q_string;
        $this_record = $db->query($q_string, "array");
        // print_r($this_record);
        return $this_record[0]["MinVal"];
        // return $q_string;
    //}
}
function LookupSDev($FormName = null, $ReturnField = null, $WhereFilterName = null, $Operator = null, $WhereSearchName = null){
     $companyFilter = "";
    
        if(Auth::hasAuth('current_user')){
    	$auth = Auth::getAuth('current_user');
    	$companyFilter = "AND company_id = {$auth['company_id']} ";
        }else{
            //$GLOBAL['company_id']
            $companyFilter = "AND company_id = {$GLOBALS['company_id']} ";
        }
        
        $arguments = func_get_args();
        //if (empty($WhereFilterName)) {
            
        //} else {
            $db = new Database();
            $q_get_table_form_name = "SELECT `id`, `form_table_name` FROM `tb_workspace` WHERE `form_name` LIKE '" . $FormName . "' " . $companyFilter;
            // print_r($q_get_table_form_name);
            $this_get_table_form = $db->query($q_get_table_form_name, "row");
            // print_r($this_get_table_form);
        
            $q_string = "SELECT STDDEV_SAMP(`$ReturnField`) AS SDVal FROM `" . $this_get_table_form["form_table_name"] . "`";
            // echo $q_string;
            $Field_Name_Counter = 2;
            $Field_Operator_Counter = 3;
            $Filter_Value_Counter = 4;
            if(isset($arguments[2])){
                $q_string .= " WHERE ";
                foreach ($arguments as $key => $value) {
                    // echo "BT".$key;
                    if($key >= 2 and $key % $Field_Name_Counter == 0){
                        $q_string .= " `".$arguments[$key]."` ";
                        $Field_Name_Counter = $Field_Name_Counter + 3;
                    }else if($key >= 3 and $key % $Field_Operator_Counter == 0){
                        $q_string .= " ".$arguments[$key]." ";
                        $Field_Operator_Counter = $Field_Operator_Counter + 3;
                    }else if($key >= 4 and $key % $Filter_Value_Counter == 0){
                        $q_string .= " '".$arguments[$key]."' ";
                        $Filter_Value_Counter = $Filter_Value_Counter + 3;
                        if($key == (count($arguments)-1) ){
                            $q_string .= " ; ";
                        }else{
                            $q_string .= " AND ";

                        }
                    }
                }
            }
            
            // echo $q_string;
            $this_record = $db->query($q_string, "array");
            // print_r($this_record);
            return $this_record[0]["SDVal"];
            // return $q_string;
        //}
}
function LookupAVG($FormName = null, $ReturnField = null, $WhereFilterName = null, $Operator = null, $WhereSearchName = null){
     $companyFilter = "";
    
        if(Auth::hasAuth('current_user')){
    	$auth = Auth::getAuth('current_user');
    	$companyFilter = "AND company_id = {$auth['company_id']} ";
        }else{
            //$GLOBAL['company_id']
            $companyFilter = "AND company_id = {$GLOBALS['company_id']} ";
        }
        
        $arguments = func_get_args();
        // if (empty($WhereFilterName)) {
            
        // } else {
            $db = new Database();
            $q_get_table_form_name = "SELECT `id`, `form_table_name` FROM `tb_workspace` WHERE `form_name` LIKE '" . $FormName . "' " . $companyFilter;
            // print_r($q_get_table_form_name);
            $this_get_table_form = $db->query($q_get_table_form_name, "row");
            // print_r($this_get_table_form);
        
            $q_string = "SELECT AVG(`$ReturnField`) AS Average FROM `" . $this_get_table_form["form_table_name"] . "`";
            // echo $q_string;
            $Field_Name_Counter = 2;
            $Field_Operator_Counter = 3;
            $Filter_Value_Counter = 4;
            if(isset($arguments[2])){
                $q_string .= " WHERE ";
                foreach ($arguments as $key => $value) {
                    // echo "BT".$key;
                    if($key >= 2 and $key % $Field_Name_Counter == 0){
                        $q_string .= " `".$arguments[$key]."` ";
                        $Field_Name_Counter = $Field_Name_Counter + 3;
                    }else if($key >= 3 and $key % $Field_Operator_Counter == 0){
                        $q_string .= " ".$arguments[$key]." ";
                        $Field_Operator_Counter = $Field_Operator_Counter + 3;
                    }else if($key >= 4 and $key % $Filter_Value_Counter == 0){
                        $q_string .= " '".$arguments[$key]."' ";
                        $Filter_Value_Counter = $Filter_Value_Counter + 3;
                        if($key == (count($arguments)-1) ){
                            $q_string .= " ; ";
                        }else{
                            $q_string .= " AND ";

                        }
                    }
                }
            }
            
            // echo $q_string;
            $this_record = $db->query($q_string, "array");
            // print_r($this_record);
            return $this_record[0]["Average"];
        // }
}
function LookupCountIf($FormName = null, $ReturnField = null, $WhereFilterName = null, $Operator = null, $WhereSearchName = null){
      $companyFilter = "";
    
    if(Auth::hasAuth('current_user')){
	$auth = Auth::getAuth('current_user');
	$companyFilter = "AND company_id = {$auth['company_id']} ";
    }else{
        //$GLOBAL['company_id']
        $companyFilter = "AND company_id = {$GLOBALS['company_id']} ";
    }
    $arguments = func_get_args();
    // if (empty($WhereFilterName)) {
        
    // } else {
        $db = new Database();
        $q_get_table_form_name = "SELECT `id`, `form_table_name` FROM `tb_workspace` WHERE `form_name` LIKE '" . $FormName . "'  " . $companyFilter;
        // print_r($q_get_table_form_name);
        $this_get_table_form = $db->query($q_get_table_form_name, "row");
        // print_r($this_get_table_form);
    
        $q_string = "SELECT COUNT(`$ReturnField`) AS Counted FROM `" . $this_get_table_form["form_table_name"] . "`";
        // echo $q_string;
        $Field_Name_Counter = 2;
        $Field_Operator_Counter = 3;
        $Filter_Value_Counter = 4;
        if(isset($arguments[2])){
            $q_string .= " WHERE ";
            foreach ($arguments as $key => $value) {
                // echo "BT".$key;
                if($key >= 2 and $key % $Field_Name_Counter == 0){
                    $q_string .= " `".$arguments[$key]."` ";
                    $Field_Name_Counter = $Field_Name_Counter + 3;
                }else if($key >= 3 and $key % $Field_Operator_Counter == 0){
                    $q_string .= " ".$arguments[$key]." ";
                    $Field_Operator_Counter = $Field_Operator_Counter + 3;
                }else if($key >= 4 and $key % $Filter_Value_Counter == 0){
                    $q_string .= " '".$arguments[$key]."' ";
                    $Filter_Value_Counter = $Filter_Value_Counter + 3;
                    if($key == (count($arguments)-1) ){
                        $q_string .= " ; ";
                    }else{
                        $q_string .= " AND ";
                    }
                }
            }
        }
        
        // echo $q_string;
        $this_record = $db->query($q_string, "array");
        // print_r($this_record);
        return $this_record[0]["Counted"];
    // }
}

function LookupAuthInfo($ReturnField = null, $WhereFilterName = null, $WhereSearchValue = null){
        $companyFilter = "";
    
        if(Auth::hasAuth('current_user')){
            $auth = Auth::getAuth('current_user');
            $companyFilter = "AND company_id = {$auth['company_id']} ";
        }else{
            //$GLOBAL['company_id']
            $companyFilter = "AND company_id = {$GLOBALS['company_id']} ";
        }
        
        if($ReturnField == null || $WhereFilterName == null){
            return "";
        }else{
            $db = new Database();
            $q_str_get_result = "SELECT `".$ReturnField."` FROM `tbuser` WHERE `".$WhereFilterName."` = '" . $WhereSearchValue . "' " . $companyFilter;
            $this_get_result = $db->query($q_str_get_result, "row");
            return $this_get_result[$ReturnField];
        }


        // // print_r($q_get_table_form_name);
        // $this_get_table_form = $db->query($q_get_table_form_name, "row");
        // // print_r($this_get_table_form);
    
        // $q_string = "SELECT * FROM `" . $this_get_table_form["form_table_name"] . "`";
        // // echo $q_string;
        // $Field_Name_Counter = 2;
        // $Field_Operator_Counter = 3;
        // $Filter_Value_Counter = 4;
        // if(isset($arguments[2])){
        //     $q_string .= " WHERE ";
        //     foreach ($arguments as $key => $value) {
        //         // echo "BT".$key;
        //         if($key >= 2 and $key % $Field_Name_Counter == 0){
        //             $q_string .= " `".$arguments[$key]."` ";
        //             $Field_Name_Counter = $Field_Name_Counter + 3;
        //         }else if($key >= 3 and $key % $Field_Operator_Counter == 0){
        //             $q_string .= " ".$arguments[$key]." ";
        //             $Field_Operator_Counter = $Field_Operator_Counter + 3;
        //         }else if($key >= 4 and $key % $Filter_Value_Counter == 0){
        //             if(is_numeric($arguments[$key])){
        //                 $q_string .= " ".$arguments[$key]." ";
        //             }else{
        //                 $q_string .= " '".$arguments[$key]."' ";
        //             }
        //             $Filter_Value_Counter = $Filter_Value_Counter + 3;
        //             if($key == (count($arguments)-1) ){
        //                 $q_string .= " ; ";
        //             }else{
        //                 $q_string .= " AND ";

        //             }
        //         }
        //     }
        // }
        
        // // echo $q_string;
        // $this_record = $db->query($q_string, "array");
        // // print_r($this_record);
        // return $this_record[0][$ReturnField];
}

function GetAuth($key_entry){
    $key_entries = array("FormAuthID","FormAuthEmail");
    if(Auth::hasAuth('current_user')){
        $auth = Auth::getAuth('current_user');
        $companyFilter = "AND company_id = {$auth['company_id']} ";
    }
    if(array_search($key_entry, $key_entries) >= 0){
        if($str_key_entry == "FormAuthID"){
            return $auth['id'];
        }else if($str_key_entry == "FormAuthEmail"){
            return $auth['email'];
        }else{
            return "";
        }
        // if(array_key_exists($key_entry, $auth)){
        //     return $auth[$key_entry];
        // }else{
        //     return "";
        // }
    }else{
        return "";
    }
}
function Total($FormName = null, $ReturnField = null, $WhereFilterName = null, $Operator = null, $WhereSearchName = null){
      $companyFilter = "";
    
        if(Auth::hasAuth('current_user')){
    	$auth = Auth::getAuth('current_user');
    	$companyFilter = "AND company_id = {$auth['company_id']} ";
        }else{
            //$GLOBAL['company_id']
            $companyFilter = "AND company_id = {$GLOBALS['company_id']} ";
        }
        
        $arguments = func_get_args();
        //if (empty($WhereFilterName)) {
            
        //} else {
            $db = new Database();
            $q_get_table_form_name = "SELECT `id`, `form_table_name` FROM `tb_workspace` WHERE `form_name` LIKE '" . $FormName . "' " . $companyFilter;
            // print_r($q_get_table_form_name);
            $this_get_table_form = $db->query($q_get_table_form_name, "row");
            // print_r($this_get_table_form);
        
            $q_string = "SELECT SUM(`$ReturnField`) AS Total FROM `" . $this_get_table_form["form_table_name"] . "`";
            // echo $q_string;
            $Field_Name_Counter = 2;
            $Field_Operator_Counter = 3;
            $Filter_Value_Counter = 4;
            if(isset($arguments[2])){
                $q_string .= " WHERE ";
                foreach ($arguments as $key => $value) {
                    // echo "BT".$key;
                    if($key >= 2 and $key % $Field_Name_Counter == 0){
                        $q_string .= " `".$arguments[$key]."` ";
                        $Field_Name_Counter = $Field_Name_Counter + 3;
                    }else if($key >= 3 and $key % $Field_Operator_Counter == 0){
                        $q_string .= " ".$arguments[$key]." ";
                        $Field_Operator_Counter = $Field_Operator_Counter + 3;
                    }else if($key >= 4 and $key % $Filter_Value_Counter == 0){
                        $q_string .= " '".$arguments[$key]."' ";
                        $Filter_Value_Counter = $Filter_Value_Counter + 3;
                        if($key == (count($arguments)-1) ){
                            $q_string .= " ; ";
                        }else{
                            $q_string .= " AND ";

                        }
                    }
                }
            }
            
            //echo '</br>\n'.$q_string;
            $this_record = $db->query($q_string, "array");
            // print_r($this_record);
            return $this_record[0]["Total"];
        //}
}
function LookupWhere($FormName = null, $ReturnField = null, $WhereFilterName = null, $Operator = null, $WhereSearchName = null){
      $companyFilter = "";
    
        if(Auth::hasAuth('current_user')){
    	$auth = Auth::getAuth('current_user');
    	$companyFilter = "AND company_id = {$auth['company_id']} ";
        }else{
            //$GLOBAL['company_id']
            $companyFilter = "AND company_id = {$GLOBALS['company_id']} ";
        }
        
        $arguments = func_get_args();
        if (empty($WhereFilterName)) {
            
        } else {
            $db = new Database();
            $q_get_table_form_name = "SELECT `id`, `form_table_name` FROM `tb_workspace` WHERE `form_name` LIKE '" . $FormName . "' " . $companyFilter;
            // print_r($q_get_table_form_name);
            $this_get_table_form = $db->query($q_get_table_form_name, "row");
            // print_r($this_get_table_form);
        
            $q_string = "SELECT * FROM `" . $this_get_table_form["form_table_name"] . "`";
            // echo $q_string;
            $Field_Name_Counter = 2;
            $Field_Operator_Counter = 3;
            $Filter_Value_Counter = 4;
            if(isset($arguments[2])){
                $q_string .= " WHERE ";
                foreach ($arguments as $key => $value) {
                    // echo "BT".$key;
                    if($key >= 2 and $key % $Field_Name_Counter == 0){
                        $q_string .= " `".$arguments[$key]."` ";
                        $Field_Name_Counter = $Field_Name_Counter + 3;
                    }else if($key >= 3 and $key % $Field_Operator_Counter == 0){
                        $q_string .= " ".$arguments[$key]." ";
                        $Field_Operator_Counter = $Field_Operator_Counter + 3;
                    }else if($key >= 4 and $key % $Filter_Value_Counter == 0){
    					if(is_numeric($arguments[$key])){
    						$q_string .= " ".$arguments[$key]." ";
    					}else{
    						$q_string .= " '".$arguments[$key]."' ";
    					}
                        $Filter_Value_Counter = $Filter_Value_Counter + 3;
                        if($key == (count($arguments)-1) ){
                            $q_string .= " ; ";
                        }else{
                            $q_string .= " AND ";

                        }
                    }
                }
            }
            
            // echo $q_string;
            $this_record = $db->query($q_string, "array");
            // print_r($this_record);
            return $this_record[0][$ReturnField];
        }
}
function Lookup($FormName = null, $ReturnField = null, $FieldName = null, $Filter = null) {
    $companyFilter = "";
    
    if(Auth::hasAuth('current_user')){
	$auth = Auth::getAuth('current_user');
	$companyFilter = "AND company_id = {$auth['company_id']} ";
    }else{
        //$GLOBAL['company_id']
        $companyFilter = "AND company_id = {$GLOBALS['company_id']} ";
    }
    if (empty($Filter)) {
        
    } else {
        $db = new Database();
        $q_get_table_form_name = "SELECT `id`, `form_table_name` FROM `tb_workspace` WHERE `form_name` LIKE '" . $FormName . "' " . $companyFilter;
        // print_r($q_get_table_form_name);
        $this_get_table_form = $db->query($q_get_table_form_name, "row");
        // print_r($this_get_table_form);
        if (!empty($ReturnField)) {
            $ReturnField = "`" . $ReturnField . "`";
        } else if (empty($ReturnField) or trim($ReturnField) == "*") {
            $ReturnField = "*";
        }

        $q_string = "SELECT $ReturnField FROM `" . $this_get_table_form["form_table_name"] . "`";

        if (!empty($FieldName) and !empty($Filter)) {
            $q_string .= " WHERE `$FieldName` LIKE '$Filter'";
        }

	$this_record = $db->query($q_string, "array");
        $ReturnField = preg_replace("/`/", "", $ReturnField);
        $collect_rec = array();
        for ($ctr = 0; $ctr < count($this_record); $ctr++) {
            array_push($collect_rec, $this_record[$ctr][$ReturnField]);
        }
        //print_r($collect_rec);
        //return $GLOBALS['company_id'];
        return join($collect_rec, "<--->");
    }
}



function ThisDate($this_date){
    if(is_date_str($this_date)){
        return strtotime($this_date);
    }else if(is_numeric($this_date)){
        return date('Y-m-d H:i:s',$this_date);
    }else{
        return "Invalid Date";
    }


    // $this_new_date = date("Y-m-d H:i",strtotime(this_date));
    // $this_sub_function = new stdClass();
    // $this_sub_function->{"formatDate"} = function (){
        // $new_here = date("Y-m-d H:i",strtotime($this_new_date));
        // return $this_new_date;
    // }
    // return $this_sub_function;
}
function StrLeft($passed_str, $number_of_chars) {
    $NOC = $number_of_chars;
    return substr($passed_str, 0, $NOC);
}

function StrConcat() {
    $args = func_get_args();
    $return_value = '';

    foreach ($args as $arg) {
        $return_value.=$arg;
    }

    return $return_value;
}

function StrRight($passed_str, $number_of_chars) {
    $NOC = $number_of_chars;
    $counted_str = StrCount($passed_str);
    $start_index = $counted_str - $NOC;
    return substr($passed_str, $start_index, $NOC);
}

function StrGet($passed_str, $start_index_char = 0, $number_of_chars = null) {
    $SIC = 0;
    $NOC = $number_of_chars;
    if (is_numeric($SIC)) {
        if ($SIC <= -1) {
            $SIC = 0;
        }
    }
    if (is_numeric($number_of_chars)) {
        $SIC = $start_index_char - 1;
        if ($NOC <= -1) {
            $NOC = 0;
        }
    }
    if (is_null($number_of_chars)) {
        return $passed_str{$SIC};
    } else {
        return substr($passed_str, $SIC, $NOC);
    }
}

function StrCount($passed_str) {
    return strlen($passed_str);
}

function GivenIf($condition, $val_true, $val_false) {
    if ($condition) {
        return $val_true;
    } else {
        return $val_false;
    }
}
//validateDate('2012-02-28 12:12:12')
function validateDate($date, $format = 'Y-m-d H:i:s'){
    $d = DateTime::createFromFormat($format, $date);
    return $d && $d->format($format) == $date;
}
function RecordCount($FormName) {
    $db = new Database();
	  $companyFilter = "";
    
    if(Auth::hasAuth('current_user')){
	$auth = Auth::getAuth('current_user');
	$companyFilter = "AND company_id = {$auth['company_id']} ";
    }else{
        //$GLOBAL['company_id']
        $companyFilter = "AND company_id = {$GLOBALS['company_id']} ";
    }
	
    $q_get_table_form_name = "SELECT `id`, `form_table_name` FROM `tb_workspace` WHERE `form_name` LIKE '" . $FormName . "'  " . $companyFilter;
    $this_get_table_form = $db->query($q_get_table_form_name, "row");
    $formname_q_count = "SELECT count(*) as RecordCount FROM " . $this_get_table_form['form_table_name'];
    $count = $db->query($formname_q_count, "row");

    return $count['RecordCount'];
}

function GetRound($given_num, $decimal_digit){
	return round(floatval($given_num),$decimal_digit);
}
Class Formula extends Database {

    public $MyFormula;
    protected $ProcessedFormula;
    public $DataFormSource;
    public $AddedDataFormSource = array();
    protected $DBase;

    public function __construct($user_defined_formula = null) {
        $this->DBase = new Database();
        $this->MyFormula = $user_defined_formula;
        functions::currentDateTime();
    }

    public function addFormSourceData($value) {
        array_push($this->AddedDataFormSource, $value);
    }
	public function updateDataFormSource($key_name , $value) {
        $this->DataFormSource[0][$key_name] = $value;
    }
    public function setSourceForm($FormName, $GetField = null, $ObjectFilter = null) {
	if(Auth::hasAuth('current_user')){
		$auth = Auth::getAuth('current_user');
		$q_get_table_form_name = "SELECT `id`, `form_table_name` FROM `tb_workspace` WHERE `form_name` LIKE '" . $FormName . "' AND company_id = {$auth['company_id']} ";
        }else{
		$q_get_table_form_name = "SELECT `id`, `form_table_name` FROM `tb_workspace` WHERE `form_name` LIKE '" . $FormName . "' ";
        }
	
	$table_name = $this->DBase->query($q_get_table_form_name, "row");

        $fieldNameSelected = $GetField;
        if (!empty($fieldNameSelected) and $fieldNameSelected != "*") {
            $fieldNameSelected = explode(",", $fieldNameSelected);
            for ($ctr = 0; $ctr < count($fieldNameSelected); $ctr++) {
                $fieldNameSelected[$ctr] = "`" . $fieldNameSelected[$ctr] . "`";
            }
            $fieldNameSelected = implode(",", $fieldNameSelected);
        } else {
            $fieldNameSelected = "*";
        }

        $q_string = "SELECT $fieldNameSelected FROM `" . $table_name["form_table_name"] . "`";
        $whereClause = ' WHERE ';

        foreach ($ObjectFilter as $key) {
            $whereClause.= $key['FieldName'] . ' ' . $key['Operator'] . ' ' . $this->DBase->escape($key['Value']) . ' AND ';
        }

        $whereClause = substr($whereClause, 0, count($whereClause) - 5);
        $q_string .= $whereClause;
        
        $source_data = $this->DBase->query($q_string, "array");

        $this->DataFormSource = $source_data;
    }

    public function getResult() {
        // print_r($this->db);
        //DITO EVAL
        eval("\$formula_result = " . $this->replaceFormulaFieldNames() . ";");
        return $formula_result;
    }

    public function replaceFormulaFieldNames() {
        $KeyWords = array("GivenIf", "StrLeft", "StrRight", "StrGet", "StrCount", "GetAuth");
        $ProcessedFormula = $this->MyFormula;
        $FC = $this->getFieldNames();
        for ($outerCtr = 0; $outerCtr < count($FC); $outerCtr++) {
            for ($ctr = 0; $ctr < count($FC[$outerCtr]); $ctr++) {
                if (in_array($FC[$outerCtr][$ctr], $KeyWords)) {
                    continue;
                } // skip the keyword dont replace
                // if (isset($_POST[$FC[$outerCtr][$ctr]])) { // if the submitted fieldname is existing
                //     print_r('test');
                //     $fn_value = $_POST[$FC[$outerCtr][$ctr]];
                //     //SANITIZING USER INPUT VALUES FOR SECURITY
                //     // $fn_value = mysqli_real_escape_string($fn_value);
                //     $field_name = $FC[$outerCtr][$ctr];
                //     $ProcessedFormula = preg_replace("/@" . $field_name . "/", $fn_value, $ProcessedFormula);
                // } else 
                if (isset($this->DataFormSource[0][$FC[$outerCtr][$ctr]]) ) {
                    //if the set source DB formname
                    $collect_value = array();
                    $string_date_stat = "false";
                    for ($ctr_data_source = 0; $ctr_data_source < count($this->DataFormSource); $ctr_data_source++) {
                        try {
                            $this_date = new DateTime($this->DataFormSource[$ctr_data_source][$FC[$outerCtr][$ctr]]);
                            $string_date_stat = "true";
                        } catch (Exception $e) {
                            $string_date_stat = "false";
                        }
                        if ($string_date_stat == "true"){
                            array_push($collect_value, "\"" . $this->DataFormSource[$ctr_data_source][$FC[$outerCtr][$ctr]] . "\"");
                        }else if (!is_numeric($this->DataFormSource[$ctr_data_source][$FC[$outerCtr][$ctr]])) {
                            array_push($collect_value, "\"" . $this->DataFormSource[$ctr_data_source][$FC[$outerCtr][$ctr]] . "\"");
                        } else {
                            array_push($collect_value, str_replace("@","^@",$this->DataFormSource[$ctr_data_source][$FC[$outerCtr][$ctr]]));
                        }
                        $string_date_stat = "false";
                    }

                    $row_value = implode("<-->", $collect_value);
                    // echo $ctr."<br/>";
                    // print_r($FC[$outerCtr][$ctr]);
                    $field_name = $FC[$outerCtr][$ctr];
                    $fn_value = "(" . $row_value . ")";
                    
                    if ($field_name == 'Requestor' || $field_name == 'Processor') {
                        $requestorDoc = new Person($this->DBase, $row_value);
                        $fn_value = "(\"" . $requestorDoc->display_name . "\")";
                    }

                    $ProcessedFormula = preg_replace("/@" . $field_name . "(?![a-zA-Z0-9_])/", $fn_value, $ProcessedFormula);
                    // echo $field_name." ahh greave";
                } else {//non existingfieldname so it would be functions/functionality keywords
                    if ($FC[$outerCtr][$ctr] == "RequestID") {
                        // $fn_value = "";
                        // $functionality_name = $FC[$outerCtr][$ctr];
                        // $ProcessedFormula = preg_replace("/@".$functionality_name."/", $fn_value, $ProcessedFormula);
                        $collect_value = array();
                        if (count($this->AddedDataFormSource) >= 1) {
                            foreach ($this->AddedDataFormSource as $added_value) {
                                array_push($collect_value, $added_value["RequestID"]);
                            }
                        }else{
                            array_push($collect_value,$this->DataFormSource[0]["ID"]);
                        }
                        // for ($ctr_data_source = 0; $ctr_data_source < count($this->DataFormSource); $ctr_data_source++) {
                        //     array_push($collect_value, $this->DataFormSource[$ctr_data_source]["ID"]);
                        // }

                        $fn_value = "(" . implode("<-->", $collect_value) . ")";
                        $functionality_name = $FC[$outerCtr][$ctr];
                        $ProcessedFormula = preg_replace("/@" . $functionality_name . "(?![a-zA-Z0-9_])/", $fn_value, $ProcessedFormula);
                    } else if ($FC[$outerCtr][$ctr] == "Status") {
                        // $fn_value = "STATUS HERE";
                        // $functionality_name = $FC[$outerCtr][$ctr];
                        // $ProcessedFormula = preg_replace("/@".$functionality_name."/", $fn_value, $ProcessedFormula);

                        $collect_value = array();
                        for ($ctr_data_source = 0; $ctr_data_source < count($this->DataFormSource); $ctr_data_source++) {
                            array_push($collect_value, $this->DataFormSource[$ctr_data_source][$FC[$outerCtr][$ctr]]);
                        }
                        $fn_value = "(" . implode("<-->", $collect_value) . ")";
                        $functionality_name = $FC[$outerCtr][$ctr];
                        $ProcessedFormula = preg_replace("/@" . $functionality_name . "(?![a-zA-Z0-9_])/", $fn_value, $ProcessedFormula);
                    } else if ($FC[$outerCtr][$ctr] == "Now") {
                        $fn_value = date("F d, Y H:i:s");
                        $functionality_name = $FC[$outerCtr][$ctr];
                        $ProcessedFormula = preg_replace("/@" . $functionality_name . "(?![a-zA-Z0-9_])/", "\"".$fn_value."\"", $ProcessedFormula);
                    } else if ($FC[$outerCtr][$ctr] == "Today") {
                        $fn_value = date("Y-m-d");//date("F d, Y");
                        $functionality_name = $FC[$outerCtr][$ctr];
                        $ProcessedFormula = preg_replace("/@" . $functionality_name . "(?![a-zA-Z0-9_])/", "\"".$fn_value."\"", $ProcessedFormula);
                    } else if (preg_match("/Department/", $FC[$outerCtr][$ctr]) != 0) {//$FC[$outerCtr][$ctr] == "Department"
                        preg_match_all("/\[.*?\]/", $FC[$outerCtr][$ctr], $attr_val);

                        // $fn_value = "my Department";
                        // $functionality_name = $FC[$outerCtr][$ctr];
                        // $ProcessedFormula = preg_replace("/@".$functionality_name."/", $fn_value, $ProcessedFormula);
                        // print_r(preg_replace("/\[|\]/", "", $attr_val[0][0]));
                        // echo "<br/>";

                        $collect_value = array();
                        for ($ctr_data_source = 0; $ctr_data_source < count($this->DataFormSource); $ctr_data_source++) {
                            $zid = $this->DataFormSource[$ctr_data_source][preg_replace("/\[|\]/", "", $attr_val[0][0])];

                            $zpersonDoc = new Person($this->DBase, $zid); //$zpersonDoc = new Person($db, $auth['id']);
                            $zuserDepartmentName = $zpersonDoc->department->name;

                            // echo "<br/>".$zuserDepartmentName;

                            array_push($collect_value, $zuserDepartmentName);
                        }
                        $fn_value = "(" . implode("<-->", $collect_value) . ")";
                        $functionality_name = $FC[$outerCtr][$ctr];

                        // echo "<br/>s".$functionality_name."s<br/>";

                        $ProcessedFormula = preg_replace("/@" . $functionality_name . "(?![a-zA-Z0-9_])/", $fn_value, $ProcessedFormula);
                    }else{
						$functionality_name = $FC[$outerCtr][$ctr];
						if(!function_exists($functionality_name)){
							//$ProcessedFormula = '("")';
							$ProcessedFormula = preg_replace("/@" . $functionality_name . "(?![a-zA-Z0-9_])/", "(\"\")", $ProcessedFormula);
						}
					}
                }
            }
        }

        preg_match_all("/\^@[A-Za-z_][A-Za-z0-9_]*/", $ProcessedFormula, $matched_at_w_backslash);
        
        $ProcessedFormula = preg_replace("/@/", "", $ProcessedFormula);

        for ($outerCtr = 0; $outerCtr < count($matched_at_w_backslash); $outerCtr++) {
            for ($ctr = 0; $ctr < count($matched_at_w_backslash[$outerCtr]); $ctr++) {
                $to_match_broken_at = str_replace("^@", "^", $matched_at_w_backslash[$outerCtr][$ctr]);
                $replace_restored_at = str_replace("^", "", $matched_at_w_backslash[$outerCtr][$ctr]);
                $ProcessedFormula = str_replace($to_match_broken_at, $replace_restored_at, $ProcessedFormula);
            }
        }
        // echo $ProcessedFormula;

       
        return $ProcessedFormula;
    }

    private function getFieldNames() {
        preg_match_all("/\^@[A-Za-z_][A-Za-z0-9_]*|@[A-Za-z_][A-Za-z0-9_]*\[[A-Za-z_][A-Za-z0-9_]*\]*|@[A-Za-z_][A-Za-z0-9_]*/", $this->MyFormula, $FieldNameCollection);
        
        for ($outerCtr = 0; $outerCtr < count($FieldNameCollection); $outerCtr++) {
            $FieldNameCollection[$outerCtr] = array_filter($FieldNameCollection[$outerCtr],function($value){
                if(is_array($value) || is_object($value)){
                    return true;
                }else if(strrpos($value, "^@") === false){
                    return true;
                }else{
                    return false;
                }
            });
        }

        for ($outerCtr = 0; $outerCtr < count($FieldNameCollection); $outerCtr++) {
            for ($ctr = 0; $ctr < count($FieldNameCollection[$outerCtr]); $ctr++) {
                $FieldNameCollection[$outerCtr][$ctr] = str_replace("@", "", $FieldNameCollection[$outerCtr][$ctr]);
            }
        }

        return $FieldNameCollection;
    }

    private function getAtFieldNames() {
        preg_match_all("/\^@[A-Za-z_][A-Za-z0-9_]*|@[A-Za-z_][A-Za-z0-9_]*\[[A-Za-z_][A-Za-z0-9_]*\]*|@[A-Za-z_][A-Za-z0-9_]*/", $this->MyFormula, $FieldNameCollection);
        for ($outerCtr = 0; $outerCtr < count($FieldNameCollection); $outerCtr++) {
            $FieldNameCollection[$outerCtr] = array_filter($FieldNameCollection[$outerCtr],function($value){
                if(is_array($value) || is_object($value)){
                    return true;
                }else if(strrpos($value, "^@") === false){
                    return true;
                }else{
                    return false;
                }
            });
        }
        for ($outerCtr = 0; $outerCtr < count($FieldNameCollection); $outerCtr++) {
            for ($ctr = 0; $ctr < count($FieldNameCollection[$outerCtr]); $ctr++) {
                $FieldNameCollection[$outerCtr][$ctr] = $FieldNameCollection[$outerCtr][$ctr];
            }
        }
        return $FieldNameCollection;
    }

    public function getProccessedFormula() {
        return $this->replaceFormulaFieldNames();
    }

    public function evaluate() {
		$returnValue = "skipped";
		try{
            if($this->getProccessedFormula()){
                eval("\$returnValue = " . $this->getProccessedFormula() . ';');
            }
		}catch(Exception $e){
			$returnValue = "Formula not valid!";
		}
		
		return $returnValue;
    }

}

?>